﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class ChiTietThongKe : BasePage
{
    //chuoi ket noi
    string connectionString = WebConfigurationManager.ConnectionStrings["DanhGiaGiaoVienConnectionString1"].ConnectionString;
   
    protected void Page_Load(object sender, EventArgs e)
    {

        string MSGV = Request.QueryString["MSGV"].ToString();
        string MaMH = Request.QueryString["MaMH"].ToString();
        string HocKy = Request.QueryString["HocKy"].ToString();
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("Select GiangVien.MSGV,HoTen,MonHoc.MaMH,TenMH,HocKy,count(distinct MSSV) as TongSVDG,sum(Diem) as TongDiem,Round(Convert(Float,(sum(Diem)))/Convert(Float,(count(distinct MSSV))),2) as DiemTB from GiangVien,ChiTietDanhGia,MonHoc Where GiangVien.MSGV='" + MSGV + "' and ChiTietDanhGia.MaMH='" + MaMH + "' and ChiTietDanhGia.HocKy='" + HocKy + "' and GiangVien.MSGV=ChiTietDanhGia.MSGV and MonHoc.MaMH = ChiTietDanhGia.MaMH Group by GiangVien.MSGV,HoTen,MonHoc.MaMH,TenMH,HocKy", conn);

        SqlCommand cmdTK = new SqlCommand("select DD.MSGV, AA.chuatot, BB.binhthuong, CC.tot, DD.rattot from (select A1.MSGV, 100.0*count(A1.Diem)/(select count(B1.MSSV) from ChiTietDanhGia B1 where A1.MSGV=B1.MSGV having count(B1.MSSV)<>0) as chuatot from ChiTietDanhGia A1 where A1.Diem=1 group by A1.MSGV) AA full join (select A2.MSGV, 100.0*count(A2.Diem)/(select count(B2.MSSV) from ChiTietDanhGia B2 where A2.MSGV=B2.MSGV having count(B2.MSSV)<>0) as binhthuong from ChiTietDanhGia A2 where A2.Diem=2 group by A2.MSGV) BB on AA.MSGV=BB.MSGV full join (select A3.MSGV, 100.0*count(A3.Diem)/(select count(B3.MSSV) from ChiTietDanhGia B3 where A3.MSGV=B3.MSGV having count(B3.MSSV)<>0) as tot from ChiTietDanhGia A3 where A3.Diem=3 group by A3.MSGV) CC on BB.MSGV=CC.MSGV full join (select A4.MSGV, 100.0*count(A4.Diem)/(select count(B4.MSSV) from ChiTietDanhGia B4 where A4.MSGV=B4.MSGV having count(B4.MSSV)<>0) as rattot from ChiTietDanhGia A4 where A4.Diem=4 group by A4.MSGV) DD on CC.MSGV=DD.MSGV where AA.MSGV='" + MSGV + "' or BB.MSGV='" + MSGV + "' or CC.MSGV='" + MSGV + "' or DD.MSGV='" + MSGV + "'", conn);

        SqlDataReader reader;
        SqlDataReader readerTK;
        //Khai báo và khởi tạo biến Connection 
        conn.Open();
        reader = cmd.ExecuteReader();
        GridView1.DataSource = reader;
        GridView1.DataBind();
        conn.Close();
        conn.Open();
        readerTK = cmdTK.ExecuteReader();
        GridView2.DataSource = readerTK;
        GridView2.DataBind();
        conn.Close();
    }
    protected void Menu1_MenuItemClick(object sender, MenuEventArgs e)
    {
        Logout();
    }
}